from flask import Flask, render_template, request, redirect, url_for, session, jsonify, flash
from datetime import datetime
import mysql.connector
from docx import Document
import os

app = Flask(__name__)
app.secret_key = 'your_secret_key'
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['ALLOWED_EXTENSIONS'] = {'docx'}

# 数据库配置
DB_CONFIG = {
    'host': '152.136.49.229',
    'user': 'yuyu',
    'password': '123456',
    'database': 'yuyude',
    #'charset': 'utf8mb4'
}

# 数据库连接函数
def get_db_connection():
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        return conn
    except mysql.connector.Error as err:
        print(f"数据库连接失败: {err}")
        return None

# 登录页面
@app.route('/', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        user_type = request.form['user_type']
        username = request.form['username']
        password = request.form['password']

        conn = get_db_connection()
        if conn:
            cursor = conn.cursor(dictionary=True)
            query = "SELECT * FROM users WHERE username=%s AND password=%s AND role=%s"
            cursor.execute(query, (username, password, user_type))
            user = cursor.fetchone()
            cursor.close()
            conn.close()

            if user:
                session['username'] = username
                session['user_type'] = user_type
                if user_type == 'student':
                    return redirect(url_for('student_dashboard', username=username))
                elif user_type == 'teacher':
                    return redirect(url_for('teacher_dashboard', username=username))
                elif user_type == 'parent':
                    return redirect(url_for('parent_dashboard', username=username))
            else:
                flash('登录失败，用户名或密码错误！', 'danger')
                return redirect(url_for('login'))
        else:
            flash('数据库连接失败，请稍后重试。', 'danger')
            return redirect(url_for('login'))

    return render_template('login.html')

# 注册页面
@app.route('/register', methods=['GET', 'POST'])
def register():
    if request.method == 'POST':
        user_type = request.form['user_type']
        username = request.form['username']
        user_id = request.form['id']
        password = request.form['password']

        conn = get_db_connection()
        if conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM users WHERE username=%s", (username,))
            existing_user = cursor.fetchone()

            if existing_user:
                flash('用户名已存在，请选择其他用户名。', 'danger')
                cursor.close()
                conn.close()
                return redirect(url_for('register'))

            try:
                cursor.execute(
                    "INSERT INTO users (username, password, id, role) VALUES (%s, %s, %s, %s)",
                    (username, password, user_id, user_type)
                )
                conn.commit()
                flash('注册成功！', 'success')
            except mysql.connector.Error as err:
                flash(f'注册失败: {err}', 'danger')
            finally:
                cursor.close()
                conn.close()
                return redirect(url_for('register_success'))

        else:
            flash('数据库连接失败，请稍后重试。', 'danger')
            return redirect(url_for('register'))

    return render_template('register.html')


@app.route('/register_success')
def register_success():
    return render_template('register_success.html')


# 忘记密码页面
@app.route('/forgot_password', methods=['GET', 'POST'])
def forgot_password():
    if request.method == 'POST':
        user_type = request.form['user_type']
        username = request.form['username']
        new_password = request.form['new_password']

        conn = get_db_connection()
        if conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT * FROM users WHERE username=%s AND role=%s", (username, user_type))
            user = cursor.fetchone()

            if user:
                try:
                    cursor.execute(
                        "UPDATE users SET password=%s WHERE username=%s AND role=%s",
                        (new_password, username, user_type)
                    )
                    conn.commit()
                    flash('密码重置成功！', 'success')
                except mysql.connector.Error as err:
                    flash(f'密码重置失败: {err}', 'danger')
            else:
                flash('用户名不存在。', 'danger')

            cursor.close()
            conn.close()
            return redirect(url_for('password_reset_success'))
        else:
            flash('数据库连接失败，请稍后重试。', 'danger')
            return redirect(url_for('forgot_password'))

    return render_template('forgot_password.html')


@app.route('/password_reset_success')
def password_reset_success():
    return render_template('password_reset_success.html')


# 学生主页
@app.route('/student_dashboard/<username>', methods=['GET', 'POST'])
def student_dashboard(username):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('login'))

    cursor = conn.cursor(dictionary=True)

    if request.method == 'POST':
        class_name = request.form.get('class_name')
        if not class_name:
            flash('班级名称不能为空。', 'danger')
        else:
            try:
                # 更新用户表中的班级信息
                cursor.execute(
                    "UPDATE users SET class_name=%s WHERE username=%s AND role='student'",
                    (class_name, username)
                )
                conn.commit()
                flash(f'成功加入班级 {class_name}！', 'success')

                # 跳转到准备考试页面
                return redirect(url_for('exam_ready', class_name=class_name, username=username))

            except mysql.connector.Error as err:
                flash(f'加入班级失败: {err}', 'danger')

    # 获取学生当前的班级信息
    cursor.execute(
        "SELECT class_name FROM users WHERE username=%s AND role='student'",
        (username,)
    )
    student_class = cursor.fetchone()
    student_class_name = student_class['class_name'] if student_class else None

    cursor.close()
    conn.close()

    return render_template('student_dashboard.html', username=username, class_name=student_class_name)


# 加入班级页面
@app.route('/join_class', methods=['GET', 'POST'])
def join_class():
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    username = session.get('username')

    if request.method == 'POST':
        class_name = request.form.get('class_name')

        if class_name:
            conn = get_db_connection()
            if conn:
                cursor = conn.cursor()
                try:
                    cursor.execute(
                        "UPDATE users SET class_name=%s WHERE username=%s AND role='student'",
                        (class_name, username)
                    )
                    conn.commit()
                    flash(f'成功加入班级 {class_name}！', 'success')
                    return redirect(url_for('student_dashboard', username=username))
                except mysql.connector.Error as err:
                    flash(f'加入班级失败: {err}', 'danger')
                finally:
                    cursor.close()
                    conn.close()
            else:
                flash('数据库连接失败，请稍后重试。', 'danger')

    return render_template('join_class.html', username=username)


# 准备考试页面
@app.route('/exam_ready/<class_name>/<username>', methods=['GET', 'POST'])
def exam_ready(class_name, username):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('login'))

    cursor = conn.cursor(dictionary=True)

    try:
        cursor.execute("SELECT name FROM exams WHERE class_name=%s AND published=TRUE", (class_name,))
        exams = cursor.fetchall()
    except mysql.connector.Error as err:
        flash(f"查询考试列表时出错: {err}", 'danger')
        exams = []

    cursor.close()
    conn.close()

    if request.method == 'POST':
        exam_name = request.form.get('exam_name')
        if exam_name:
            # 跳转到考试页面
            return redirect(url_for('exam_page', class_name=class_name, exam_name=exam_name))

    return render_template('exam_ready.html', class_name=class_name, username=username, exams=exams)


# 加入考试界面
@app.route('/join_exam/<username>', methods=['GET', 'POST'])
def join_exam(username):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    if request.method == 'POST':
        exam_name = request.form.get('exam_name')
        if exam_name:
            # 处理加入考试的逻辑，比如检查考试是否存在，并将学生加入该考试
            flash(f'成功加入考试 {exam_name}！', 'success')
            return redirect(url_for('student_dashboard', username=username))

    return render_template('join_exam.html', username=username)


# 考试页面
@app.route('/exam/<class_name>/<exam_name>', methods=['GET', 'POST'])
def exam_page(class_name, exam_name):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('login'))

    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT id FROM exams WHERE class_name=%s AND name=%s", (class_name, exam_name))
    exam = cursor.fetchone()
    exam_id = exam['id']

    cursor.execute("SELECT * FROM questions WHERE exam_id=%s", (exam_id,))
    questions = cursor.fetchall()

    # 检查是否已经设置了考试开始时间
    if 'exam_start_time' not in session:
        session['exam_start_time'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S.%f')

    remaining_seconds = 3600  # 假设剩余考试时间为 1 小时
    if request.method == 'POST':
        # 处理考试提交逻辑
        return redirect(url_for('render_submit_exam', class_name=class_name, exam_name=exam_name))

    return render_template(
        'exam.html',
        remaining_time=remaining_seconds,
        class_name=class_name,
        exam_name=exam_name,
        questions=questions
    )

@app.route('/random_exam/', methods=['POST'])
def random_exam():
    try:
        student_id = request.form['student_id']
        print(f"Received student_id: {student_id}")

        exam_name = 'Sample Exam'
        num_choices = 2
        num_true_false = 1
        num_subjective = 2

        exam = generate_exam(exam_name, num_choices, num_true_false, num_subjective)

        if exam:
            print("Exam generated successfully.")
            return render_template('examm.html', exam=exam, exam_name=exam_name, student_id=student_id)
        else:
            print("Failed to generate exam.")
            return '生成试卷失败'
    except Exception as e:
        print(f"Error in random_exam: {e}")
        return 'Error occurred while generating the exam.'


def generate_exam(exam_name, num_choices=5, num_true_false=5, num_subjective=5):
    try:
        conn = get_db_connection()
        if not conn:
            print("Database connection failed.")
            return None

        cursor = conn.cursor()

        # 随机选择选择题
        cursor.execute("SELECT * FROM questionsxuanze ORDER BY RAND() LIMIT %s", (num_choices,))
        choices = cursor.fetchall()
        print(f"选择题数量: {len(choices)}")

        # 随机选择判断题
        cursor.execute("SELECT * FROM questionstiankong ORDER BY RAND() LIMIT %s", (num_true_false,))
        true_false = cursor.fetchall()
        print(f"判断题数量: {len(true_false)}")

        # 随机选择主观题
        cursor.execute("SELECT * FROM questionszhuguan ORDER BY RAND() LIMIT %s", (num_subjective,))
        subjective = cursor.fetchall()
        print(f"主观题数量: {len(subjective)}")

        conn.commit()

        exam = {
            'choices': choices,
            'true_false': true_false,
            'subjective': subjective
        }
        return exam

    except mysql.connector.Error as err:
        print(f"Error in generate_exam: {err}")
        return None
    finally:
        cursor.close()
        conn.close()

    return exam


# 提交考试页面
@app.route('/submit_exam', methods=['POST'])
def submit_exam():
    student_id = request.form['student_id']
    exam_name = request.form['exam_name']

    # 连接数据库
    conn = mysql.connector.connect(
        host="152.136.49.229",
        user="yuyu",
        password="123456",  # 替换为实际的数据库密码
        database="yuyude",
    )
    cursor = conn.cursor()

    total_score = 0

    try:
        # 处理选择题
        for question_id in request.form.getlist('choice_question_id'):
            student_answer = request.form.get(f'choice_{question_id}')
            correct_answer = request.form.get(f'correct_choice_{question_id}')

            if student_answer == correct_answer:
                total_score += 1  # 假设每题一分

            save_student_answer(cursor, student_id, exam_name, question_id, student_answer)

        # 处理判断题
        for question_id in request.form.getlist('true_false_question_id'):
            student_answer = request.form.get(f'true_false_{question_id}')
            correct_answer = request.form.get(f'correct_true_false_{question_id}')

            if student_answer == correct_answer:
                total_score += 1  # 假设每题一分

            save_student_answer(cursor, student_id, exam_name, question_id, student_answer)

        # 处理主观题
        for question_id in request.form.getlist('subjective_question_id'):
            student_answer = request.form.get(f'subjective_{question_id}')
            # 主观题通常需要人工评分，此处不比较正确答案，但保存学生答案
            save_student_answer(cursor, student_id, exam_name, question_id, student_answer)

        # 保存总分
        save_total_score(cursor, student_id, exam_name, total_score)
        conn.commit()

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        cursor.close()
        conn.close()

    return f'考试完成！您的总分是: {total_score}'


def save_student_answer(cursor, student_id, exam_name, question_id, student_answer):
    sql = """
    INSERT INTO p_student (student_id, exam_name, question_id, student_answer)
    VALUES (%s, %s, %s, %s)
    """
    cursor.execute(sql, (student_id, exam_name, question_id, student_answer))


def save_total_score(cursor, student_id, exam_name, total_score):
    sql = """
    INSERT INTO p_score (student_id, exam_name, total_score)
    VALUES (%s, %s, %s)
    """
    cursor.execute(sql, (student_id, exam_name, total_score))


@app.route('/render_submit_exam/<class_name>/<exam_name>', methods=['GET'])
def render_submit_exam(class_name, exam_name):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    try:
        exam_start_time_str = session.get('exam_start_time')
        if not exam_start_time_str:
            raise ValueError("考试开始时间未找到")

        exam_start_time = datetime.strptime(exam_start_time_str, '%Y-%m-%d %H:%M:%S.%f')
        elapsed_time = datetime.now() - exam_start_time
        elapsed_seconds = int(elapsed_time.total_seconds())

        session.pop('exam', None)
        session.pop('exam_start_time', None)

        return render_template('exam_finished.html', class_name=class_name, exam_name=exam_name,
                               elapsed_seconds=elapsed_seconds, username=session['username'])
    except Exception as e:
        return render_template('exam_finished.html', class_name=class_name, exam_name=exam_name, error=str(e),
                               username=session['username'])


# 考试完成页面
@app.route('/exam_finished/<class_name>/<exam_name>', methods=['GET'])
def exam_finished(class_name, exam_name):
    if 'user_type' not in session or session['user_type'] != 'student':
        return redirect(url_for('login'))

    return render_template(
        'exam_finished.html',
        class_name=class_name,
        exam_name=exam_name,
        username=session['username']
    )


# 老师主页
@app.route('/teacher_dashboard/<username>', methods=['GET', 'POST'])
def teacher_dashboard(username):
    if 'user_type' not in session or session['user_type'] != 'teacher':
        return redirect(url_for('login'))

    if request.method == 'POST':
        if 'add_exam' in request.form:
            return redirect(url_for('create_exam', username=username))
        elif 'manage_class' in request.form:
            return redirect(url_for('manage_class', username=username))

    return render_template('teacher_dashboard.html', username=username)


# # 创建考试页面
# @app.route('/create_exam/<username>', methods=['GET', 'POST'])
# def create_exam(username):
#     if 'user_type' not in session or session['user_type'] != 'teacher':
#         return redirect(url_for('login'))
#
#     if request.method == 'POST':
#         exam_title = request.form.get('exam_title')
#         exam_description = request.form.get('exam_description')
#         class_name = request.form.get('class_name')
#
#         conn = get_db_connection()
#         if conn:
#             cursor = conn.cursor()
#             try:
#                 cursor.execute(
#                     "INSERT INTO exams (name, description, class_name, published) VALUES (%s, %s, %s, %s)",
#                     (exam_title, exam_description, class_name, False)  # 初始未发布
#                 )
#                 exam_id = cursor.lastrowid  # 获取新创建的考试ID
#                 conn.commit()
#                 flash('考试创建成功！现在可以发布考试。', 'success')
#                 return redirect(url_for('publish_exam', exam_id=exam_id))  # 跳转到发布考试
#             except mysql.connector.Error as err:
#                 flash(f'考试创建失败: {err}', 'danger')
#             finally:
#                 cursor.close()
#                 conn.close()
#         else:
#             flash('数据库连接失败，请稍后重试。', 'danger')
#
#     return render_template('create_exam.html', username=username)
#

# 发布考试
@app.route('/publish_exam/<int:exam_id>', methods=['POST'])
def publish_exam(exam_id):
    if 'user_type' not in session or session['user_type'] != 'teacher':
        return redirect(url_for('login'))

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('teacher_dashboard', username=session['username']))

    cursor = conn.cursor()
    try:
        cursor.execute(
            "UPDATE exams SET published=TRUE WHERE id=%s",
            (exam_id,)
        )
        conn.commit()
        flash('考试已发布！', 'success')
    except mysql.connector.Error as err:
        flash(f'发布考试失败: {err}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('teacher_dashboard', username=session['username']))


# 管理班级页面
@app.route('/manage_class/<username>', methods=['GET', 'POST'])
def manage_class(username):
    if 'user_type' not in session or session['user_type'] != 'teacher':
        return redirect(url_for('login'))

    if request.method == 'POST':
        class_name = request.form.get('class_name')
        if class_name:
            return redirect(url_for('class_management', class_name=class_name))
        else:
            flash('请输入班级名称', 'danger')

    return render_template('manage_class.html', username=username)


# 班级管理详情页面
@app.route('/class_management/<class_name>', methods=['GET', 'POST'])
def class_management(class_name):
    if 'user_type' not in session or session['user_type'] != 'teacher':
        return redirect(url_for('login'))

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('teacher_dashboard', username=session.get('username')))

    cursor = conn.cursor(dictionary=True)

    # 查询 users 表中所有属于该班级的学生
    cursor.execute(
        """
        SELECT username, id 
        FROM users 
        WHERE class_name = %s AND role = 'student'
        """,
        (class_name,)
    )
    students = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('class_management.html', class_name=class_name, students=students)


# 删除学生的路由
@app.route('/delete_student/<int:student_id>/<class_name>', methods=['POST'])
def delete_student(student_id, class_name):
    if 'user_type' not in session or session['user_type'] != 'teacher':
        return redirect(url_for('login'))

    print(f"Deleting student with ID: {student_id} from class: {class_name}")  # 调试信息

    conn = get_db_connection()
    if not conn:
        flash('数据库连接失败，请稍后重试。', 'danger')
        return redirect(url_for('class_management', class_name=class_name))

    cursor = conn.cursor()
    try:
        # 删除与学生相关联的记录
        cursor.execute("DELETE FROM student_classes WHERE student_id = %s", (student_id,))
        conn.commit()

        # 删除学生
        cursor.execute("DELETE FROM users WHERE id = %s AND role = 'student'", (student_id,))
        conn.commit()
        flash('学生已成功删除！', 'success')
    except mysql.connector.Error as err:
        flash(f'删除学生失败: {err}', 'danger')
    finally:
        cursor.close()
        conn.close()

    return redirect(url_for('class_management', class_name=class_name))


# 管理员主页
@app.route('/parent_dashboard/<username>')
def parent_dashboard(username):
    if 'user_type' not in session or session['user_type'] != 'parent':
        return redirect(url_for('login'))

    return render_template('parent_dashboard.html', username=username)


# 记录作弊行为
@app.route('/record_cheating', methods=['POST'])
def record_cheating():
    if 'username' in session:
        username = session['username']
        conn = get_db_connection()
        if conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute(
                "SELECT cheating_count FROM users WHERE username=%s",
                (username,)
            )
            result = cursor.fetchone()
            cheating_count = result.get('cheating_count', 0) if result else 0

            cheating_count += 1

            cursor.execute(
                "UPDATE users SET cheating_count=%s WHERE username=%s",
                (cheating_count, username)
            )
            conn.commit()
            cursor.close()
            conn.close()

            max_cheat_attempts = 2
            if cheating_count > max_cheat_attempts:
                return jsonify({'terminate': True})
            return jsonify({'terminate': False})
        else:
            return jsonify({'error': '数据库连接失败'}), 500
    return jsonify({'error': '用户未登录'}), 401


# 终止考试
@app.route('/terminate_exam')
def terminate_exam():
    session.pop('exam', None)
    flash("由于检测到多次作弊行为，您的考试已被终止。", 'danger')
    return redirect(url_for('login'))


# 登出页面
@app.route('/logout')
def logout():
    session.clear()  # 清除所有 session 数据
    flash('您已成功退出登录。', 'success')
    return redirect(url_for('login'))

#######################################################################################
# 允许的文件类型
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in app.config['ALLOWED_EXTENSIONS']


# 提取题目
def extract_questions(file_path, question_type):
    document = Document(file_path)
    questions = []
    current_question = None

    for para in document.paragraphs:
        text = para.text.strip()

        if question_type == 'questionsxuanze':  # 选择题
            if text.startswith("Q:"):
                if current_question:
                    questions.append(current_question)
                current_question = {
                    'text': text[2:].strip(),
                    'options': {'A': None, 'B': None, 'C': None, 'D': None},
                    'correct_answer': None
                }
            elif text.startswith("A:") or text.startswith("B:") or text.startswith("C:") or text.startswith("D:"):
                option_key = text[0]
                if option_key in current_question['options']:
                    current_question['options'][option_key] = text[2:].strip()
            elif text.startswith("Answer:"):
                if current_question:
                    current_question['correct_answer'] = text[7:].strip()

            # 确保在题目完整时添加到问题列表中
            if current_question and all(current_question['options'].values()) and current_question['correct_answer']:
                questions.append(current_question)
                current_question = None

        elif question_type == 'questionstiankong':  # 判断题
            if text.startswith("Q:"):
                if current_question:
                    questions.append(current_question)
                current_question = {
                    'text': text[2:].strip(),
                    'correct_answer': None
                }
            elif text.startswith("答案:"):
                if current_question:
                    answer_text = text[3:].strip()
                    if answer_text in ['对', '错']:
                        current_question['correct_answer'] = answer_text
            # 确保在题目完整时添加到问题列表中
            if current_question and current_question['correct_answer']:
                questions.append(current_question)
                current_question = None

        elif question_type == 'questionszhuguan':  # 主观题
            if text and (text[0].isdigit() and text[1] == '.'):
                if current_question:
                    questions.append(current_question)
                current_question = {'text': text, 'answers': []}
            elif text:
                if current_question:
                    current_question['answers'].append(text)
            # 确保在题目完整时添加到问题列表中
            if current_question and current_question['answers']:
                questions.append(current_question)
                current_question = None

    # 添加最后一个问题（如果存在）
    if current_question:
        questions.append(current_question)

    return questions


# 将题目插入到数据库
def insert_questions(exam_id, questions, question_type, exam_name):
    conn = mysql.connector.connect(
        host="152.136.49.229",
        user="yuyu",
        password="123456",  # 替换为实际的数据库密码
        database="yuyude",
    )
    cursor = conn.cursor()

    try:
        for question in questions:
            if question_type == 'questionsxuanze':  # 选择题
                sql = """
                INSERT INTO questionsxuanze (exam_id, question_text, option_a, option_b, option_c, option_d, correct_answer, question_type)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                """
                values = (
                    exam_id, question['text'],
                    question['options']['A'], question['options']['B'],
                    question['options']['C'], question['options']['D'],
                    question['correct_answer'], '选择题'  # 确保这个值在 ENUM 定义中
                )
            elif question_type == 'questionstiankong':  # 判断题
                sql = """
                INSERT INTO questionstiankong (exam_id, question_text, correct_answer, question_type)
                VALUES (%s, %s, %s, %s)
                """
                values = (exam_id, question['text'], question['correct_answer'], '填空题')  # 确保这个值在 ENUM 定义中
            elif question_type == 'questionszhuguan':  # 主观题
                sql = """
                INSERT INTO questionszhuguan (exam_id, question_text, correct_answer, question_type)
                VALUES (%s, %s, %s, %s)
                """
                for answer in question['answers']:
                    values = (exam_id, question['text'], answer, '主观题')  # 确保这个值在 ENUM 定义中
                    cursor.execute(sql, values)
                continue
            cursor.execute(sql, values)

        conn.commit()
    except mysql.connector.Error as err:
        print(f"Error: {err}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


@app.route('/create_exam', methods=['GET', 'POST'])
def create_exam():
    if request.method == 'POST':
        # 获取表单提交的数据
        exam_title = request.form['exam-title']
        exam_desc = request.form['exam-desc']
        exam_date = request.form['exam-date']

        # 连接数据库并插入数据
        conn = mysql.connector.connect(
            host="152.136.49.229",
            user="yuyu",
            password="123456",  # 替换为实际的数据库密码
            database="yuyude",
        )
        cursor = conn.cursor()
        cursor.execute('INSERT INTO exams (title, description, exam_date) VALUES (%s, %s, %s)',
                       (exam_title, exam_desc, exam_date))
        exam_id = cursor.lastrowid
        conn.commit()
        cursor.close()
        conn.close()

        # 插入数据后，重定向回首页或其他页面

        return redirect(url_for('teacher_dashboard', username=session['username']))  # 返回教师控制台
    return render_template('create_exam.html')



@app.route('/', methods=['GET', 'POST'])
def index():
    return render_template('create_exam.html')


@app.route('/generate_exam', methods=['GET','POST'])
def generate_exam():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    if request.method == 'POST':
        # 选择题库处理
        xuanze_count = int(request.form['xuanze_count'])
        xuanze_randomize = 'xuanze_randomize' in request.form
        xuanze_selected_ids = request.form.getlist('xuanze_question_ids')

        tiankong_count = int(request.form['tiankong_count'])
        tiankong_randomize = 'tiankong_randomize' in request.form
        tiankong_selected_ids = request.form.getlist('tiankong_question_ids')

        zhuguan_count = int(request.form['zhuguan_count'])
        zhuguan_randomize = 'zhuguan_randomize' in request.form
        zhuguan_selected_ids = request.form.getlist('zhuguan_question_ids')

        # 创建试卷
        title = "My New Exam"
        description = "This is a dynamically generated exam."
        cursor.execute('INSERT INTO papers (title, description) VALUES (%s, %s)', (title, description))
        paper_id = cursor.lastrowid

        # 处理选择题库
        if xuanze_randomize:
            cursor.execute('SELECT * FROM questionsxuanze ORDER BY RAND() LIMIT %s', (xuanze_count,))
        else:
            if xuanze_selected_ids:
                cursor.execute('SELECT * FROM questionsxuanze WHERE id IN ({",".join(xuanze_selected_ids)})')
        xuanze_questions = cursor.fetchall()

        # 处理填空题库
        if tiankong_randomize:
            cursor.execute('SELECT * FROM questionstiankong ORDER BY RAND() LIMIT %s', (tiankong_count,))
        else:
            if tiankong_selected_ids:
                cursor.execute('SELECT * FROM questionstiankong WHERE id IN ({",".join(tiankong_selected_ids)})')
        tiankong_questions = cursor.fetchall()

        # 处理主观题库
        if zhuguan_randomize:
            cursor.execute('SELECT * FROM questionszhuguan ORDER BY RAND() LIMIT %s', (zhuguan_count,))
        else:
            if zhuguan_selected_ids:
                cursor.execute('SELECT * FROM questionszhuguan WHERE id IN ({",".join(zhuguan_selected_ids)})')
        zhuguan_questions = cursor.fetchall()

        # 保存所有题目到试卷
        all_questions = xuanze_questions + tiankong_questions + zhuguan_questions

        for question in all_questions:
            cursor.execute('''
                    INSERT INTO examquestions (paper_id, question_text, option_a, option_b, option_c, option_d, correct_answer, question_type)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ''', (
                paper_id,
                question['question_text'],
                question.get('option_a'),
                question.get('option_b'),
                question.get('option_c'),
                question.get('option_d'),
                # question['correct_answer'],
                question.get('correct_answer'),
                question['question_type']  # 添加 question_type 列
                # question.get('question_type')
            ))

        conn.commit()
        cursor.close()
        conn.close()

        return redirect(url_for('exam_list', paper_id=paper_id))

    # 如果是 GET 请求，显示所有可选题目
    cursor.execute('SELECT * FROM questionsxuanze')
    xuanze_questions = cursor.fetchall()

    cursor.execute('SELECT * FROM questionstiankong')
    tiankong_questions = cursor.fetchall()

    cursor.execute('SELECT * FROM questionszhuguan')
    zhuguan_questions = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('generate_exam.html',
                            xuanze_questions = xuanze_questions,
                            tiankong_questions = tiankong_questions,
                            zhuguan_questions = zhuguan_questions)

##########################################################yuyu增加随机抽题——预览题目
@app.route('/exam_list/<int:paper_id>')
def exam_list(paper_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    # 获取指定试卷的所有题目
    cursor.execute('SELECT * FROM examquestions WHERE paper_id = %s', (paper_id,))
    questions = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('exam_list.html', questions=questions, exam_id=paper_id)

@app.route('/edit_exam_question/<int:question_id>', methods=['GET', 'POST'])
def edit_exam_question(question_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute('SELECT * FROM examquestions WHERE id = %s', (question_id,))
    question = cursor.fetchone()

    if request.method == 'POST':
        sql = '''
        UPDATE examquestions
        SET question_text = %s, option_a = %s, option_b = %s, option_c = %s, option_d = %s, correct_answer = %s
        WHERE id = %s
        '''
        cursor.execute(sql, (
            request.form['question_text'],
            request.form.get('option_a'),
            request.form.get('option_b'),
            request.form.get('option_c'),
            request.form.get('option_d'),
            request.form['correct_answer'],
            question_id
        ))

        conn.commit()
        cursor.close()
        conn.close()

        return redirect(url_for('exam_list', paper_id=question['paper_id']))

    cursor.close()
    conn.close()

    return render_template('edit_exam_question.html', question=question)

@app.route('/preview_exam/<int:paper_id>', methods=['GET'])
def preview_exam(paper_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute('SELECT * FROM papers WHERE id = %s', (paper_id,))
    exam = cursor.fetchone()

    cursor.execute('SELECT * FROM examquestions WHERE paper_id = %s', (paper_id,))
    questions = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template('preview_exam.html', exam=exam, questions=questions)

@app.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files or request.files['file'].filename == '':
        return '没有选择文件'

    file = request.files['file']
    question_type = request.form['question_type']
    exam_id = 1

    # 检查 exam_id 是否存在
    conn = mysql.connector.connect(
        host="152.136.49.229",
        user="yuyu",
        password="123456",  # 替换为实际的数据库密码
        database="yuyude",
    )
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM exams WHERE id = %s', (exam_id,))
    exam_exists = cursor.fetchone()
    cursor.close()
    conn.close()

    if not exam_exists:
        return '指定的考试ID不存在'

    if file and allowed_file(file.filename):
        filename = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
        file.save(filename)

        if not os.path.exists(filename):
            return '文件保存失败'

        try:
            questions = extract_questions(filename, question_type)
            print(f"提取的问题: {questions}")
        except Exception as e:
            print(f"提取问题时发生错误: {e}")
            return '提取问题失败'

        try:
            insert_questions(exam_id, questions, question_type, 'Sample Exam')
        except Exception as e:
            print(f"插入问题时发生错误: {e}")
            return '插入问题失败'

        return redirect(url_for('upload_success'))

    return '文件格式不正确'


@app.route('/upload_success')
def upload_success():
    return render_template('success.html')


if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000, debug=True)
